<?php
/**
* Parse a given QIF file and load the transactions in the DB.
* QIF format is line based with each line being one of the following:
* 
* ^ end of items, D date, T amount, C cleared, P payee, M memo, A address, L category, S cartegory split, E memo split, $ amount split
*
* As this is a very poor format, we are loosy as well and we load what we can parse, everything is ignored
* Date management is a nightmare so we will load as we can and offer the possibility to edit the dates
*  
*  @author       Fabrice Douteaud <clearbudget@douteaud.com>
* @package      actions
* @access       public
*/

/***********************************************************************

  Copyright (C) 2008  Fabrice douteaud (clearbudget@douteaud.com)

    This file is part of ClearBudget.

    ClearBudget is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    ClearBudget is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with ClearBudget.  If not, see <http://www.gnu.org/licenses/>.


************************************************************************/
//prevent direct access
if(!defined('ENGINEON')) die('Direct access forbidden');
global $context;
$db = class_db::getInstance();

// a flag that tells us we have an invalid date format
$invalidDateFormat = false;

// First we check if we have the correct data from the client
// check if we get there from a POST with a file uploaded
if($_SERVER["REQUEST_METHOD"] != 'POST' || !isset($_FILES["qiffile"])) {
  $return['error'] = true;
  $return['msg'] = $keys->error_ImproperCall;
  class_debug::addMsg(__FILE__, 'called to upload file not in POST and no file is present', DEBUGINFO);
  return;
  }

// check if the file has been uploaded correctly
if(!file_exists($_FILES["qiffile"]['tmp_name'])) {
  $return['error'] = true;
  $return['msg'] = $keys->error_ImproperQifFile;
  return;
  }

// a bit debuging as to check the locale parameters (important for date parsing)
class_debug::addMsg(__FILE__, 'Parsing QIF dates as '.$context->QIFLocaleType, DEBUGDEBUG);
$dateFormat = $context->QIFLocaleType;
/**
	* clean the amount as to always have a valid float 
	*  	
	* @param string the amount
	* @return float the clean amount
	*/		
function cleanAmount($amount){
  $amount = trim($amount);
  if(preg_match("~^([0-9]+|(?:(?:[0-9]{1,3}([.,' ]))+[0-9]{3})+)(([.,])[0-9]{1,2})?$~", $amount, $r)){
    if(!empty($r['2'])){
      $pre = preg_replace("~[".$r['2']."]~", "", $r['1']);
      }
    else {
      $pre = $r['1'];
      }
    if(!empty($r['4'])){
      $post = ".".preg_replace("~[".$r['4']."]~", "", $r['3']);
      }
    else {
      $post = false;
      }
    $amount = $pre.$post;
    return $amount;
    }
  return false;
  }

/**
	* clean a string from any extra white spaces 
	*  	
	* @param string the string
	* @return string the clean string
	*/	
function clean($string) {
  $string = strtolower(trim($string));
  while(1) {
    $tmp = str_replace('  ', ' ', $string);
    if($tmp != $string) $string = $tmp;
    else break;
    }
  return $string; 
  }

/**
 * This function 'tries' to format the date as our DB expects it
 * 
 * This is a bit of a nightmare as QIF format is very loosy on that side
 * Date can be DD/MM/YY, MM/DD/YY, DD/MM/YYYY, MM/DD/YYYY, or with - instead of /
 * Dates in US QIF files are usually in the format MM/DD/YY, although
 * four-digit years are not uncommon.  Dates sometimes occur without the
 * slash separator, or using other separators in place of the slash,
 * commonly '-' and '.'.  US Quicken seems to be using the ' to indicate
 * post-2000 two-digit years (such as 01/01'00 for Jan 1 2000).  Some
 * banks appear to be using a completely undifferentiated numeric string
 * formateed YYYYMMDD in downloaded QIF files.
 * 	
 * European QIF files may have dates in the DD/MM/YY format.
 * check these for more info:
 *  http://svn.gnucash.org/trac/browser/gnucash/branches/1.8/src/import-export/qif-import/file-format.txt
 *  http://web.intuit.com/support/quicken/docs/d_qif.html   
 */
function formatDate($string, &$invalidDateFormat) {
  global $context;
  $defaultDate = '2000-01-01';
  $returnDate = $defaultDate;
  // if no dates is given, then we use the default
  if($string == '') return $defaultDate;
  
  // let's try to reformat the date as to get YYYY/MM/DD
  
  // split it with /
  $date = explode('/', $string);
  
  // if date aren't splitted by /, we try with -
  if($date[0] == $string) $date = explode('-', $string);
  
  // test if we successfully splitted the date
  if($date[0] == $string || count($date) != 3) {
    // ok, we really cannot parse this stuff so we use the default date 
    return $defaultDate;
    }
  
  // if year is on 2 digit we fake it to a year 2000 something
  if(strlen($date[2]) == 2) $date[2] = '20'.$date[2];
  
  // format the date depending on the QIF Locale flag
  switch($context->QIFLocaleType) {
    case 'eu':
      // here we expect the month to be in place #1 :: DD/MM/YYYY
      if($date[1] > 12) $invalidDateFormat = true;
      $returnDate =  $date[2].'-'.$date[1].'-'.$date[0];
    break;
    case 'us':
    //default:
      // here we expect the month to be in place #0 :: MM/DD/YYYY
      if($date[0] > 12) $invalidDateFormat = true;
      $returnDate =  $date[2].'-'.$date[0].'-'.$date[1];
    break;
    }
  return $returnDate;
  }
  
// load the file in an array
$lines = @file($_FILES["qiffile"]['tmp_name']);
// if loading was unsuccessful, we exit with error (file type not correct most probably)
if($lines == false) {
  $return['error'] = true;
  $return['msg'] = $keys->error_ImproperQifFile2;
  return;
  }
// check if the first line starts with '!Type:' which should be the case in a valid QIF file
if(substr($lines[0], 0, 6) != '!Type:') {
  $return['error'] = true;
  $return['msg'] = $keys->error_ImproperQifFile3;
  return;
  }
// get the type, must be Bank
//$type = trim(substr($lines[0], 6, strlen($lines[0])));
// as this is sometimes translated, this is completely useless so we do not check for the Type :-(

// array to hold each item from the file
$item = array();
// counter of lines processed
$lineCounter = 1;
// counter of item inserted successfuly
$insertCounter = 0;  
// counter of item not inserted because duplicated
$duplicateCounter = 0;  
// array to hold and compare magic number (but still allows n equal fields per file)
$magics = array();
// get today's date
$today = date("Y-m-d");

// insert a record in the batch records
$db->addFields('originalFileName', $_FILES['qiffile']['name']);
$db->addFields('importCount', 0);
$db->addFields('importDuplicate', 0);
$db->addFields('importDate', $today);
$importId = $db->insert('t_imports');

// go line by line to process the file
foreach($lines as $line) {
  $lineCounter++;
  if($line != '') {
    // get the line identifier
    $id = substr($line, 0, 1);
    // get the content of that line
    $content = trim(substr($line, 1, strlen($line)));
    switch ($id) {
      case '^':
        set_time_limit(10); 
        // add the null category
        $item['category'] = '1';
        // add the import batch ID
        $item['importId'] = $importId;
        // add the current date
        $item['createDate'] = $today;
        // create the magic string
        $magic = $item['operationDate'].$item['amount'].$item['payee'];
        //echo '$magic: '.$magic.'<br/>';
        $magics[$magic][] = $lineCounter;
        $magicCounter = count($magics[$magic]);
        //echo '$magicCounter: '.$magicCounter.'<br/>';
        if($magicCounter > 1) {
          // add the checksum from the magic + the magic counter
          $item['checksum'] = md5($magic).'-'.$magicCounter;
          }
        else {
          // add the checksum from the magic
          $item['checksum'] = md5($magic);
          }
        
        // insert the row
        foreach($item as $field=>$value) {
          $db->addFields($field, $value);
          }
        
        if($db->insert('t_items') !== false) {
          //echo 'inserted<br/>';
          $insertCounter++;
          }
        else {
          $duplicateCounter++;
          $magics[$magic] = null;
          }
        $item = null;
      break;
      case 'D':
        $item['operationDate'] = formatDate($content, $invalidDateFormat);
      break;
      case 'T':
        $item['debit'] = '0';
        if($content == '') continue;
        $st = substr($content, 0, 1);
        if($st == '-') {
          $item['debit'] = '1';
          $content = substr($content, 1, strlen($content)-1);
          }
        $item['amount'] = cleanAmount($content);
      break;
      case 'C':
        $item['cleared'] = $content;
      break;
      case 'P':
        $item['payee'] = clean($content);
      break;
      case 'M':
        $item['memo'] = clean($content);
      break;
      default:
      break;
      } // switch
    } // if
  } // foreach

if(!$invalidDateFormat) {
  // update the import record with the data
  $db->addFields('importCount', $insertCounter);
  $db->addFields('importDuplicate', $duplicateCounter);
  $db->addWhere('id', '=', $importId);
  $db->update('t_imports');


  // let's update the time range if that has changed from the new data
  $report = class_report::getInstance();
  $report->setLimitMonth($context->range, true);

  $return['msg'] = sprintf($_FILES['qiffile']['name'].' - '.$keys->text_importedTransactions.': %d, '.$keys->text_duplicatedRecords.': %d', $insertCounter, $duplicateCounter);
  $return['error'] = false;
  $return['importCount'] = $insertCounter;
  $return['importId'] = $importId;
  }
else {
  // remove everything as this is not a valid import (dates are malformed)
  $db->addWhere('id', '=', $importId);
  $db->delete('t_imports');
  $db->addWhere('importId', '=', $importId);
  $db->delete('t_items');

  $return['error'] = true;
  $return['msg'] = $keys->error_ImproperQifFile4;
  }
?>